
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"


int putItem(sqlite3 *dba)
{
    sqlite3_stmt *stmt;
    char *sql;
    int ret;

    sql = "INSERT INTO space VALUES(1, 'atto');COMMIT;";
    ret = sqlite3_prepare(dba, sql, strlen(sql), &stmt, NULL);
    if(ret != SQLITE_OK)
        return 1;

    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE)
        return 2;

    sql = "INSERT INTO space VALUES(2, 'mike')";
    ret = sqlite3_prepare(dba, sql, strlen(sql), &stmt, NULL);
    if(ret != SQLITE_OK)
        return 3;

    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE)
        return 4;

    return 0;
}


int getItem(sqlite3 *dba)
{
    sqlite3_stmt *stmt;
    char *sql;
    int ret;

    sql = "SELECT * FROM space";
    ret = sqlite3_prepare(dba, sql, strlen(sql), &stmt, NULL);
    if(ret != SQLITE_OK)
        return 1;

    while(1)
    {
        int id;
        const unsigned char *tag;

        ret = sqlite3_step(stmt);
        if (ret != SQLITE_ROW)
            break;

        id  = sqlite3_column_int(stmt, 0);
        tag = sqlite3_column_text(stmt, 1);

        printf("get: id = %d, tag = %s\n", id, tag);
    }

    return 0;
}


int delItem(sqlite3 *dba)
{
    sqlite3_stmt *stmt;
    char *sql;
    int ret;

    sql = "DELETE FROM space WHERE id = 1";
    ret = sqlite3_prepare(dba, sql, strlen(sql), &stmt, NULL);
    if(ret != SQLITE_OK)
        return 1;

    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE)
        return 2;

    sql = "DELETE FROM space WHERE id = 2";
    ret = sqlite3_prepare(dba, sql, strlen(sql), &stmt, NULL);
    if(ret != SQLITE_OK)
        return 3;

    ret = sqlite3_step(stmt);
    if(ret != SQLITE_DONE)
        return 4;

    return 0;
}


int main()
{
    sqlite3 *dba;
    char *sql;
    int ret;

    /* 
     * to open on-disk db, try open "./test.db"
     * to open in-memory db, try open ":memory:"
     * to open temporary db, try open ""
     */
    ret = sqlite3_open_v2(":memory:", &dba, SQLITE_OPEN_READWRITE, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "open db err: %s\n", sqlite3_errmsg(dba));
        return 1;
    }

	ret = sqlite3_threadsafe();

	ret = sqlite3_get_autocommit(dba);

    sql = "CREATE TABLE space(id INTEGER, tag TEXT, PRIMARY KEY(id DESC))";
    ret = sqlite3_exec(dba, sql, NULL, NULL, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "create table err: %s\n", sqlite3_errmsg(dba));
        sqlite3_close(dba);
        return 2;
    }

    ret = putItem(dba);
    if (ret != 0)
    {
        fprintf(stderr, "put item err: %s\n", sqlite3_errmsg(dba));
        sqlite3_close(dba);
        return 3;
    }

    ret = getItem(dba);
    if (ret != 0)
    {
        fprintf(stderr, "get item err: %s\n", sqlite3_errmsg(dba));
        sqlite3_close(dba);
        return 4;
    }
	
	ret = delItem(dba);
	if (ret != 0)
    {
        fprintf(stderr, "del item err: %s\n", sqlite3_errmsg(dba));
        sqlite3_close(dba);
        return 5;
    }

    sql = "DROP TABLE space";
    ret = sqlite3_exec(dba, sql, NULL, NULL, NULL);
    if (ret != SQLITE_OK)
    {
        fprintf(stderr, "drop table err: %s\n", sqlite3_errmsg(dba));
        sqlite3_close(dba);
        return 2;
    }

    sqlite3_close(dba);
    getchar();
    return 0;
}
